"""
Case Type   : GSC功能模块
Case Name   : 主备事务内，分别查询表后，主机进行表ddl修改操作后commit，验证备机表缓存是否一致
Create At   : 2023/5/19
Owner       : opentestcase024
Description :
    step1:打开gsc开关;
    step2:重启数据库生效;
    step3:创建数据库;
    step4:创建表，并插入数据;
    step5:以线程开启事务，主机对表进行alter操作后commit;
    step6:备机在主机事务执行ddl前后，分别进行表查询;
    step7:备机再次进行表查询;
Expect      :
    step1:打开gsc开关; expect:修改成功
    step2:重启数据库生效; expect:重启成功
    step3:创建数据库; expect:创建成功
    step4:创建表，并插入数据; expect:操作成功
    step5:以线程开启事务，主机对表进行alter操作后commit; expect: 操作成功
    step6:备机在主机事务执行ddl前后，分别进行表查询; expect:ddl前查询正确，ddl后查询报错
    step7:备机再次进行表查询; expect:查询结果正确
History     :
    Modified by peilinqian at 2025/1/21:teardown增加集群主备恢复动作，以防对其他用例产生影响
    modified by peilinqian at 2024-6-17: 用例适配资源池化
"""
import os
import time
import unittest

from testcase.utils.ComThread import ComThread
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

pri_sh = CommonSH('PrimaryDbUser')
node_num = pri_sh.get_node_num()


@unittest.skipIf(node_num < 3, '非1主2备环境不执行')
class GscTestCase(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)}:start----')
        self.log.info("----this is setup----")
        self.db_name = 'db_gsc0034'
        self.tb_name = "tb_gsc0034"
        self.index_name = "index_gsc0034"
        self.com = Common()
        self.constant = Constant()
        self.sta_sh = CommonSH('Standby1DbUser')

    def test_main(self):
        step_txt = '----step0:查看数据库状态是否正常;----'
        self.log.info(step_txt)
        status_result = pri_sh.get_db_cluster_status('status')
        self.assertTrue(status_result, '执行失败:' + step_txt)
        step_txt = '----step0:查看enable_global_syscache初始配置值;----'
        self.log.info(step_txt)
        self.init_para1 = self.com.show_param("enable_global_syscache")

        if self.init_para1 == 'off':
            step_txt = '----step1:打开gsc开关; expect:修改成功----'
            self.log.info(step_txt)
            result = pri_sh.execute_gsguc('set',
                                          self.constant.GSGUC_SUCCESS_MSG,
                                          f"enable_global_syscache= on")
            self.assertTrue(result, '执行失败:' + step_txt)

            step_txt = '----step2:重启数据库生效; expect:重启成功----'
            self.log.info(step_txt)
            restart = pri_sh.restart_db_cluster()
            self.assertTrue(restart, '执行失败:' + step_txt)
            self.is_reset_para = True
        else:
            step_txt = '----gsc开关设置正常，跳过step1、step2----'
            self.log.info(step_txt)
            self.is_reset_para = False

        step_txt = '----step3:创建数据库; expect:创建成功----'
        self.log.info(step_txt)
        create_db = f'drop database if exists {self.db_name};' \
                    f'create database {self.db_name};'
        create_result = pri_sh.execut_db_sql(create_db)
        self.log.info(create_result)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, create_result,
                      '执行失败:' + step_txt)

        step_txt = '----step4:创建表，并插入数据; expect:操作成功----'
        self.log.info(step_txt)
        self.create_sql = f"drop table if exists {self.tb_name};" \
                          f"create table {self.tb_name}(jid int,jn int ,name int)" \
                          f"partition by range (jid) subpartition by range(jn)(" \
                          f"partition hr1 values less than(100)(" \
                          f"subpartition hr11 values less than(100)," \
                          f"subpartition hr12 values less than(300))," \
                          f"partition hr2 values less than(300)(" \
                          f"subpartition hr21 values less than(100)," \
                          f"subpartition hr22 values less than(maxvalue)));" \
                          f"select * from {self.tb_name};" \
                          f"insert into {self.tb_name} select generate_series(1,200)," \
                          f"generate_series(1,200), generate_series(1,200);"
        result = pri_sh.execut_db_sql(self.create_sql,
                                      dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, result,
                      "执行失败" + step_txt)
        self.assertIn('0 rows', result, "执行失败" + step_txt)

        step5_txt = '----step5:以线程开启事务，主机对表进行alter操作后commit，expect: 操作成功---'
        self.log.info(step5_txt)
        alter_sql = f'start transaction;' \
                    f'select * from {self.tb_name} limit 1;' \
                    f'select pg_sleep(10);' \
                    f'alter table {self.tb_name} add (bak1 varchar(100),bak2 char);' \
                    f'select * from {self.tb_name} limit 1;' \
                    f'select pg_sleep(10);' \
                    f'commit;'
        session1 = ComThread(pri_sh.execut_db_sql,
                             args=(alter_sql, '', self.db_name,))
        session1.setDaemon(True)
        session1.start()
        time.sleep(2)

        step_txt = '----step6:备机在主机事务执行ddl前后，分别进行表查询; ' \
                   'expect:ddl前查询正确，ddl后查询报错----'
        self.log.info(step_txt)
        alter_sql = f"start transaction;" \
                    f"select * from {self.tb_name} limit 1;" \
                    f"select pg_sleep(15);" \
                    f"select * from {self.tb_name} limit 1;" \
                    f"commit;"
        sta1_result = self.sta_sh.execut_db_sql(alter_sql,
                                                dbname=f'{self.db_name}')
        self.log.info(sta1_result)
        suc_flag1 = 'jid | jn | name'
        self.assertEqual(2, sta1_result.count(suc_flag1),"执行失败" + step_txt)

        step_txt = '----step7:备机再次进行表查询; expect:查询结果正确----'
        self.log.info(step_txt)
        select_sql = f"select * from {self.tb_name} limit 1;"
        result = self.sta_sh.execut_db_sql(select_sql,
                                           dbname=f'{self.db_name}')
        self.log.info(result)
        suc_flag2 = 'jid | jn | name | bak1 | bak2'
        self.assertEqual(suc_flag2, result.splitlines()[0].strip(),
                         "执行失败" + step_txt)

        session1.join()
        session1_result = session1.get_result()
        self.log.info(session1_result)

        self.assertEqual(suc_flag1, session1_result.splitlines()[1].strip(),
                         "执行失败" + step5_txt)
        self.assertEqual(suc_flag2, session1_result.splitlines()[12].strip(),
                         "执行失败" + step5_txt)

    def tearDown(self):
        self.log.info('----this is tearDown----')
        step1_txt = '----清理数据库; expect:清理成功----'
        self.log.info(step1_txt)
        drop_cmd = f"drop database if exists {self.db_name};"
        drop_result = pri_sh.execut_db_sql(drop_cmd)
        self.log.info(drop_result)

        if self.is_reset_para:
            self.step2_txt = '----恢复参数为初始值并查询; expect:设置成功----'
            self.log.info(self.step2_txt)
            self.msg1 = pri_sh.execute_gsguc(
                'set', self.constant.GSGUC_SUCCESS_MSG,
                f"enable_global_syscache ={self.init_para1}")
            self.step3_txt = '----重启数据库生效; expect:重启成功----'
            self.log.info(self.step3_txt)
            self.restart = pri_sh.restart_db_cluster()
            self.step4_txt = '----查询数据库状态; expect:状态正常----'
            self.log.info(self.step4_txt)
            status = pri_sh.exec_cm_ctl(mode='query', param='-Cv')
            self.log.info(status)
            p_status = status.splitlines()[-1].split('|')[0].split('6001')[-1]
            if 'Primary' in p_status and "cluster_state   : Normal" in status:
                self.log.info('---集群状态正常，无需恢复为初始状态---')
            else:
                recovery = pri_sh.exec_cm_ctl(mode='switchover',
                                                   param='-a')
                self.log.info(recovery)
                time.sleep(10)
            self.status_result = pri_sh.get_db_cluster_status('status')

        step_txt = '----teardown断言----'
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, drop_result,
                      "执行失败" + step_txt + step1_txt)
        if self.is_reset_para:
            self.assertTrue(self.msg1, '执行失败:' + self.step2_txt)
            self.assertTrue(self.restart, '执行失败:' + self.step3_txt)
            self.assertTrue(self.status_result, '执行失败:' + self.step4_txt)

        self.log.info(f'----{os.path.basename(__file__)}:end----')
